package com.wlyuan.open.scanner.dao.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.wlyuan.open.scanner.application.annotation.DataScope;
import com.wlyuan.open.scanner.dao.entity.CompanyDO;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.List;

/**
 * <p>
 * Mapper 接口
 * </p>
 *
 * @author yuanjie
 * @since 2022-09-23
 */
public interface CompanyMapper extends BaseMapper<CompanyDO> {

    String QUERY_SQL = "SELECT tm.Company_ID, tm.Company_GUID, tm.Company_Name, tm.Company_Personal, tm.Company_Industry, tm.Company_Logo, tm.Company_Web, tm.Company_Description, " +
            "tm.Company_Scale, tm.Company_Map, tm.Company_ProvinceID, tm.Company_CityID, tm.Company_DistrictID, tm.Company_TownID, tm.Company_Keywords, tm.Company_Contact, tm.Company_Gender," +
            " tm.Company_Phone, tm.Company_Status, tm.Company_OfficeNo, tm.Company_Fax, tm.Company_Address, tm.Company_Zip, tm.Company_Mail, tm.Company_ClientCode, tm.Company_ShortName, " +
            "tm.Company_EnName, tm.Company_ShortEnName, tm.Company_Master, tm.Company_License, tm.Company_LicensePic, tm.Company_Weixin, tm.Company_Bank, tm.Company_BankAccount, " +
            "tm.Company_RefereCompany, tm.Company_CreatorID, tm.Company_InsertTime, tm.Company_Updater, tm.Company_UpdateTime, tm.Company_Invalid, tm.Company_Comments, tm.opt_status, " +
            "tm.Company_IsGroup, tm.Company_IsZones, tm.Company_BillDay, tm.Company_DriversLicense, tm.Company_DrivingLicense, tm.Company_CardID, tm.Company_DriversLicensePic, " +
            "tm.Company_DrivingLicensePic, tm.Company_CardIDPic, tm.Company_Verify, tm.Company_Signtype, ISNULL(tm.Company_Source, 0) as companySource, tm.Company_Code, tm.Company_Province, tm.Company_City, " +
            "tm.Company_District, tm.Company_Check, tm.Company_BankFirst, tm.Company_BankName, tm.Company_VerifyTime, tm.Company_InvalidCp, tm.Company_WorkingAddress, " +
            "tm.Company_AddressLongitude, tm.Company_AddressLatitude, tm.Company_domainName, tm.Company_refusalReason, tm.Company_isApiToken";


    String COMPANY_TENANT_SQL = QUERY_SQL
            + ",ISNULL(ap.Area_Name,'') as province, ISNULL(ac.Area_Name,'') as city,ISNULL(ad.Area_Name,'') as district " +
            "FROM TMS_Company tm " +
            "LEFT JOIN TMS_BasicArea ap on tm.Company_ProvinceID  = ap.Area_ID " +
            "LEFT JOIN TMS_BasicArea ac on tm.Company_CityID  = ac.Area_ID " +
            "LEFT JOIN TMS_BasicArea ad on tm.Company_DistrictID  = ad.Area_ID ";

    String COMPANY_TENANT_WHERE_SQL = "WHERE tm.Company_Personal = 0 ";

    String DRIVER_TENANT_SQL = QUERY_SQL
            + ", td.driverPersonalSNCheck as idcard,td.driverFacePhotoCheck as idcardFront,td.driverNationalPhotoCheck as idcardBack,td.driverUserBirthday as birthday," +
            "td.driverPhotoCheck as personFront,td.driverRefuseReason as rejectReason,td.driverPersonalSNrefuse as driverPersonalSnRefuse," +

            "td.driverCarDrivingPhotoCheck as drivingLicenseFront,td.driverCarPhotoCheck as vehicleFront,td.driverCarSNCheck as plateNo,td.driverCarTypeCheck as vehicleType," +
            "td.driverCarModelCheck as vehicleModel,td.driverCarRegisterDateCheck as registerDate,td.driverCarIssueDateCheck as issueDate,td.driverCarDrivingNameCheck as ownerName," +
            "td.driverRefuseReason as driveringLicenseRejectReason,td.driverCarSNRefuse as driverCarSnRefuse," +

            "td.driverSNCheck as licenseNo,td.driverLicenseTypeCheck as driverType,td.driverCardPhotoCheckSubpage as driverLicenseBack,td.driverCardPhotoCheck as driverLicenseFront," +
            "td.driverFirstDateCheck as driverIssueDate,td.driverStartDateCheck as driverValidFromDate,td.driverEndDateCheck as driverValidToDate,td.driverNameCheck as driverOwnerName," +
            "td.driverRefuseReason as driverLicenseRejectReason,td.driverSNRefuse as driverSnRefuse," +

            "td.transCertificateNo as certNo,td.transCertificateType as businessScope,td.transCertificatePhotoCheck as certFront,td.transCertificateTimefiled as transIssueDate," +
            "td.transCertificateValidity as transValidToDate, td.transCertificateName as transOwnerName," +

            "car.Car_Length as length,car.Car_Weight as loadWeight, car.Car_Volume as volume,car.Car_Model as model,car.Car_PurchaseTime as buyDate,car.Car_Insurance as insuranceEndDate," +

            "tu.User_Nickname as nick ,tu.User_Gender as gender,tu.User_OpenId as openId,tu.User_Portrait as avatar " +
            "FROM TMS_Company tm " +
            "LEFT JOIN TMS_DriverPersonalCheck td on tm.Company_ID = td.driverCompanyID and td.driverRealEffectiveStatus = 1 and td.driverInvalid = 0" +
            "LEFT JOIN (select t.User_ID,t.User_CompanyID,t.User_Nickname,t.User_Gender,t.User_OpenId,t.User_Portrait from TMS_User t where t.User_CompanyID > 0 and t.User_ID in (select min(t2.User_ID) FROM TMS_User t2 where t2.User_OpenId IS NOT NULL and t2.User_OpenId !='' group by User_CompanyID )) tu on tm.Company_ID = tu.User_CompanyID and tu.User_OpenId IS NOT NULL and tu.User_OpenId !='' " +
            "LEFT JOIN (select tmc.Car_ID,tmc.Car_CompanyID,tmc.Car_SN ,tmc.Car_Brand ,tmc.Car_Type ,tmc .Car_Length ,tmc .Car_Weight ,tmc.Car_Volume ,tmc.Car_Model ,tmc.Car_PurchaseTime ," +
            "tmc.Car_Insurance " +
            "from TMS_MCar tmc where tmc.Car_CompanyID > 0 and tmc.Car_ID in (select max(tmcc.Car_ID) FROM TMS_MCar tmcc group by Car_CompanyID)) car on tm.Company_ID  = car.Car_CompanyID ";

    String DRIVER_TENANT_WHERE_SQL = "WHERE tm.Company_Personal = 1 ";

    String ORDER_BY = " ORDER BY tm.Company_ID";

    /**
     * 公司租户
     *
     * @return
     */
    @Select(COMPANY_TENANT_SQL + COMPANY_TENANT_WHERE_SQL + ORDER_BY)
    @DataScope(column = "Company_ID", alias = "tm")
    List<CompanyDO> companyTenantList();

    /**
     * 司机租户
     *
     * @return
     */
    @Select(DRIVER_TENANT_SQL + DRIVER_TENANT_WHERE_SQL + ORDER_BY)
    @DataScope(column = "Company_ID", alias = "tm")
    List<CompanyDO> driverTenantList();

    /**
     * 单个公司租户
     *
     * @param companyId
     * @return
     */
    @Select(COMPANY_TENANT_SQL + " WHERE tm.Company_ID = #{companyId}")
    CompanyDO getCompanyTenantById(Long companyId);

    /**
     * 单个司机租户
     *
     * @param companyId
     * @return
     */
    @Select(DRIVER_TENANT_SQL + " WHERE tm.Company_ID = #{companyId}")
    CompanyDO getDriverTenantById(Long companyId);


    @Select("SELECT Company_ID FROM TMS_Company WHERE Company_InsertTime >= #{startTime} and Company_InsertTime <= #{endTime} ")
    List<Long> getByTime(@Param("startTime") String startTime, @Param("endTime") String endTime);

}
